/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package koala.objects;

import java.sql.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.ArrayList;

/**
 *
 * @author asus
 */
public class DatabaseConnection {
    
    public DatabaseConnection(){}
    
    public Connection establishConnection() throws Exception{
        Class.forName("com.mysql.jdbc.Driver");
        String conStr = "jdbc:mysql://127.0.0.1:3306/koalaproject?user=root&password=";

        Connection con = DriverManager.getConnection(conStr);
        System.out.println("connection done");

        return con;
    }
    
    public ArrayList<User> userList() throws Exception {
        Connection con = establishConnection();
        String stSel = "SELECT * FROM koalaproject.users;";
        Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
        ResultSet rs = stmt.executeQuery(stSel);
        
        rs.beforeFirst();
        
        ArrayList<User> users = new ArrayList<>();
        
        while (rs.next()) {
            boolean state = rs.getString("userStatus").equals("ACTIVE");
            users.add(new User(rs.getString("userID"), rs.getString("userName"), state));
        }
        
        return users;
    }
    
    public ArrayList<Project> projectList() throws Exception {
        Connection con = establishConnection();
        PasswordFunction passFxn = new PasswordFunction();
        String stSel = "SELECT * FROM koalaproject.project;";
        Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
        ResultSet rs = stmt.executeQuery(stSel);
        
        rs.beforeFirst();
        
        ArrayList<Project> projects = new ArrayList<>();
        
        while (rs.next()) {
            ProjectLeader leader = new ProjectLeader();
            String projLeader = rs.getString("projectLeader");
            
            for(User in: userList()){
                if(projLeader.equals(in.getUserId())){
                    leader = new ProjectLeader(in.getUserId(), passFxn.retrievePassword(in.getUserId()) ,in.getUsername(), userProjects(in.getUserId()), in.getState());
                }
            }
            
            boolean state = rs.getString("projectStatus").equals("COMPLETED");
            projects.add(new Project(rs.getString("projectName"), leader, state));
        }
        
        return projects;
    }
    
    public ArrayList<Project> userProjects(String userID) throws Exception{
        Connection con = establishConnection();
        String stSel = "SELECT project.projectName FROM koalaproject.project NATURAL JOIN koalaproject.projectmembers WHERE projectmembers.userID = " + userID + ";";
        Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
        ResultSet rs = stmt.executeQuery(stSel);
        
        rs.beforeFirst();
        
        ArrayList<Project> userProjects = new ArrayList<>();
        
        while (rs.next()) {
            Project proj = new Project();
            for(Project in : projectList()){
                if(rs.getString("projectName").equals(in.getProjectName())){
                    proj = in;
                }
            }
            userProjects.add(proj);
        }
        
        return userProjects;
    }
    
    public ArrayList<User> projectMembers(String projectName) throws Exception {
        Connection con = establishConnection();
        String stSel = "SELECT * FROM koalaproject.users NATURAL JOIN koalaproject.projectmembers NATURAL JOIN koalaproject.project WHERE project.projectName = " + projectName + ";";
        Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
        ResultSet rs = stmt.executeQuery(stSel);
        
        rs.beforeFirst();
        
        ArrayList<User> projectMembers = new ArrayList<>();
        
        while (rs.next()) {
            boolean state = rs.getString("userStatus").equals("ACTIVE");
            projectMembers.add(new User(rs.getString("userID"), rs.getString("userName"), state));
        }
        
        return projectMembers;
    }
    
    public void changeUserState(String userID, boolean state) throws Exception {
        Connection con = establishConnection();
        
        String userStatus = "INACTIVE";
        
        if(state){
            userStatus = "ACTIVE";
        }
        
        PreparedStatement psu = con.prepareStatement("UPDATE koalaproject.users SET userStatus = ? WHERE userID = ? ");
        psu.setString(1, userStatus);
        psu.setString(2, userID);
        psu.executeUpdate();
    }
    
    public void changeProjectState(String projectID, boolean state) throws Exception {
        Connection con = establishConnection();
        
        String projStatus = "ON-GOING";
        
        if(!state){
            projStatus = "COMPLETED";
        }
        
        PreparedStatement psu = con.prepareStatement("UPDATE koalaproject.project SET projectStatus = ? WHERE projectID = ? ");
        psu.setString(1, projStatus);
        psu.setString(2, projectID);
        psu.executeUpdate();
    }
    
    public void addUser(User usr, String password) throws Exception {
        Connection con = establishConnection();
        
        PreparedStatement psi;
        String stIns = "INSERT INTO koalaproject.users(userID, userName, userStatus, userPosition) VALUES (?,?,?,?)";
        psi = con.prepareStatement(stIns);
        psi.setString(1, usr.getUserId());
        psi.setString(2, usr.getUsername());
        psi.setString(3, "INACTIVE");
        psi.setString(4, "USER");
        psi.executeUpdate();
        
        PasswordFunction pFxn = new PasswordFunction();
        pFxn.saveAndEncryptPassword(usr.getUserId(), password);
    }
    
    public void addProject(Project proj) throws Exception {
        Connection con = establishConnection();
        
        int counter = projectList().size();
        
        PreparedStatement psu;
        String stIns = "INSERT INTO koalaproject.project(projectID, projectName, projectLeader, projectStatus) VALUES (?,?,?,?)";
        psu = con.prepareStatement(stIns);
        psu.setString(1, "PROJ_" + counter);
        psu.setString(2, proj.getProjectName());
        psu.setString(3, proj.getProjectLeader().getUserId());
        psu.setString(4, "ON-GOING");
        psu.executeUpdate();
    }
    
    public void addProjectMembers(Project proj, ArrayList<User> members) throws Exception{
        Connection con = establishConnection();
        
        String stSel = "SELECT * FROM koalaproject.project;";
        Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
        ResultSet rs = stmt.executeQuery(stSel);
        
        rs.beforeFirst();
        
        ArrayList<String> projectNames = new ArrayList<>();
        
        while (rs.next()) {
            projectNames.add(rs.getString("projectName"));
        }
        
        PreparedStatement psu;
        String stIns = "INSERT INTO koalaproject.projectmembers(projectID, userID) VALUES (?,?)";
        
        ArrayList<Project> projs = projectList();
        
        psu = con.prepareStatement(stIns);
        for(User in: members){
            for(String name : projectNames){
                if(name.equals(proj.getProjectName())){
                    psu.setString(1, rs.getString("projectID"));
                }
            }
            psu.setString(2, in.getUserId());
        }
        psu.executeUpdate();
    }
}